1. Field of the Invention
The present invention generally relates to data processing systems, and more particularly to representing hierarchical data from a database query result in a tabular display, such as a spreadsheet.
2. Description of the Related Art
Mapping the results of a relational database query to a spreadsheet is often a straightforward process, as both formats are basically tabular. Typically, each field in a result table is mapped to a column in the spreadsheet. This way each row in the query result is mapped to a row in the spreadsheet. So long as the fields in the query result provide simple values such as a text-string, a date, an integer, or real number, etc., all of the values in the query result may readily be presented in the spreadsheet. However, problems arise when a field of the result set includes more complex information, such as a hierarchical data structure. For example, one of the fields in the result set may be an XML document. Since XML data is hierarchical, each element inside the XML document could potentially map to multiple rows in the spreadsheet.
Spreadsheet applications may provide some features for presenting an XML document in a spreadsheet. These features, however, are generally only applicable for a single XML document and not for multiple XML documents included a field of a query result. Further, even with this approach (commonly referred to as XML document mapping) there is no provision for mapping an XML document with an embedded hierarchy of data to the cells of a spreadsheet. Instead, spreadsheet applications may treat the XML value in the query result as just a character string and add it to the spreadsheet cell as such. Since the XML data actually represents a hierarchy of elements, this approach is insufficient for creating reports on XML data. Another approach is to view the XML result field in hierarchical grids, where each element in the hierarchy is represented by a row in the grid. When a row or field is clicked, the row or field may expand to an encapsulated grid set. That is, the field is presented essentially as a spreadsheet within a spreadsheet. The problem with this approach is that it is not very useful for reporting since it is still representing the data in a hierarchical fashion. The structure of the grid is fixed and it directly maps to the hierarchy of the result and not to a user defined report format which is much more tabular.
Another approach is to flatten the XML inside the query using known SQL/XML functions like XMLTable. This produces a tabular result set with many of the field values repeated for each row. There are a number of drawbacks with this approach, however. First, since each row may contain a lot of repeated data, this makes a report appear unnecessarily cluttered. Second, the query to create a flattened result set can be quite complicated since the query needs to be composed to manipulate the hierarchical data and flatten it out as part of the query. Thus, this approach ends up doing unnecessary transformation to the XML data just because the reporting tool cannot handle hierarchical result sets, resulting in more expensive queries. Nevertheless, many reporting tools follow this approach since it is least disruptive for existing infrastructures.
Accordingly, as demonstrated by the forgoing discussion, there remains a need for techniques for presenting query result data that includes fields of hierarchical data in a tabular representation, such as a spreadsheet.